package zry.Achievement.Patent;

import zry.Search.SearchCriteriaPatent;
import zry.Util.DBUtil;
import Connection.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class PatentDAOImpl extends DAOBase implements PatentDAO {
    private static final String PATENT_INSERT_SQL = "INSERT INTO patent_table(achievement_id,patent_name,patent_type,patent_number,patent_time,patent_status,contribution,supporting) VALUES(?,?,?,?,?,?,?,?)";
    private static final String PATENT_UPDATE_SQL = "UPDATE patent_table SET patent_name=?,patent_type=?,patent_number=?,patent_time=?,patent_status=?,contribution=?,supporting=? WHERE achievement_id=?";
    private static final String PATENT_SELECT_SQL = "SELECT * FROM patent_table WHERE achievement_id=?";
    private static final String PATENT_SELECT_ALL_SQL = "SELECT * FROM patent_table";

    @Override
    public boolean addPatent(Patent patent) {
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(PATENT_INSERT_SQL);
            psmt.setString(1, DBUtil.fixSqlFieldValue(patent.getA_id()));
            psmt.setString(2, DBUtil.fixSqlFieldValue(patent.getP_name()));
            psmt.setString(3, DBUtil.fixSqlFieldValue(patent.getP_type()));
            psmt.setString(4, DBUtil.fixSqlFieldValue(patent.getP_number()));
            psmt.setDate(5, patent.getP_time());
            psmt.setString(6, DBUtil.fixSqlFieldValue(patent.getP_status()));
            psmt.setString(7, DBUtil.fixSqlFieldValue(patent.getP_contribution()));
            psmt.setString(8, DBUtil.fixSqlFieldValue(patent.getP_supporting()));
            psmt.executeUpdate();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    @Override
    public boolean updatePatent(Patent patent) {
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(PATENT_UPDATE_SQL);
            psmt.setString(1, DBUtil.fixSqlFieldValue(patent.getP_name()));
            psmt.setString(2, DBUtil.fixSqlFieldValue(patent.getP_type()));
            psmt.setString(3, DBUtil.fixSqlFieldValue(patent.getP_number()));
            psmt.setDate(4, patent.getP_time());
            psmt.setString(5, DBUtil.fixSqlFieldValue(patent.getP_status()));
            psmt.setString(6, DBUtil.fixSqlFieldValue(patent.getP_contribution()));
            psmt.setString(7, DBUtil.fixSqlFieldValue(patent.getP_supporting()));
            psmt.setString(8, DBUtil.fixSqlFieldValue(patent.getA_id()));
            psmt.executeUpdate();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    @Override
    public Patent getPatent(String achievement_id) {
        Patent patent = null;
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(PATENT_SELECT_SQL);
            psmt.setString(1, achievement_id);
            ResultSet rs = psmt.executeQuery();
            if (rs.next()) {
                patent = new Patent();
                patent.setA_id(rs.getString("achievement_id"));
                patent.setP_name(rs.getString("patent_name"));
                patent.setP_type(rs.getString("patent_type"));
                patent.setP_number(rs.getString("patent_number"));
                patent.setP_time(rs.getDate("patent_time"));
                patent.setP_status(rs.getString("patent_status"));
                patent.setP_contribution(rs.getString("contribution"));
                patent.setP_supporting(rs.getString("supporting"));
            }
            rs.close();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return patent;
    }

    public List<Patent> getAllPatents() {
        List<Patent> patents = new ArrayList<>();
        Connection con = null;
        try {
            con = getConnection();
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(PATENT_SELECT_ALL_SQL);
            while (rs.next()) {
                Patent patent = new Patent();
                patent.setA_id(rs.getString("achievement_id"));
                patent.setP_name(rs.getString("patent_name"));
                patent.setP_type(rs.getString("patent_type"));
                patent.setP_number(rs.getString("patent_number"));
                patent.setP_time(rs.getDate("patent_time"));
                patent.setP_status(rs.getString("patent_status"));
                patent.setP_contribution(rs.getString("contribution"));
                patent.setP_supporting(rs.getString("supporting"));
                patents.add(patent);
            }
            rs.close();
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return patents;
    }

    @Override
    public List<Patent> findPatents(SearchCriteriaPatent searchCriteriaPatent) {
        List<Patent> patents = new ArrayList<>();
        Connection con = null;
        try {
            // 构建SQL语句
            StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM patent_table WHERE 1=1");
            if (searchCriteriaPatent.getA_id() != null) {
                sqlBuilder.append(" AND achievement_id LIKE ?");
            }
            if (searchCriteriaPatent.getP_name() != null) {
                sqlBuilder.append(" AND patent_name LIKE ?");
            }
            if (searchCriteriaPatent.getP_type() != null) {
                sqlBuilder.append(" AND patent_type LIKE ?");
            }
            if (searchCriteriaPatent.getP_number() != null) {
                sqlBuilder.append(" AND patent_number LIKE ?");
            }
            if (searchCriteriaPatent.getP_time() != null) {
                sqlBuilder.append(" AND patent_time LIKE ?");
            }
            if (searchCriteriaPatent.getP_status() != null) {
                sqlBuilder.append(" AND patent_status LIKE ?");
            }
            if (searchCriteriaPatent.getP_contribution() != null) {
                sqlBuilder.append(" AND contribution LIKE ?");
            }
            if (searchCriteriaPatent.getP_supporting() != null) {
                sqlBuilder.append(" AND supporting LIKE ?");
            }
            String sql = sqlBuilder.toString();

            // 执行查询
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(sql);
            int index = 1;
            if (searchCriteriaPatent.getA_id() != null) {
                psmt.setString(index++, searchCriteriaPatent.getA_id());
            }
            if (searchCriteriaPatent.getP_name() != null) {
                psmt.setString(index++, searchCriteriaPatent.getP_name());
            }
            if (searchCriteriaPatent.getP_type() != null) {
                psmt.setString(index++, searchCriteriaPatent.getP_type());
            }
            if (searchCriteriaPatent.getP_number() != null) {
                psmt.setString(index++, searchCriteriaPatent.getP_number());
            }
            if (searchCriteriaPatent.getP_time() != null) {
                psmt.setDate(index++, searchCriteriaPatent.getP_time());
            }
            if (searchCriteriaPatent.getP_status() != null) {
                psmt.setString(index++, searchCriteriaPatent.getP_status());
            }
            if (searchCriteriaPatent.getP_contribution() != null) {
                psmt.setString(index++, searchCriteriaPatent.getP_contribution());
            }
            if (searchCriteriaPatent.getP_supporting() != null) {
                psmt.setString(index++, searchCriteriaPatent.getP_supporting());
            }
            ResultSet rs = psmt.executeQuery();
            while (rs.next()) {
                String a_id = rs.getString("achievement_id");
                String p_name = rs.getString("patent_name");
                String p_type = rs.getString("patent_type");
                String p_number = rs.getString("patent_number");
                Date p_time = rs.getDate("patent_time");
                String p_status = rs.getString("patent_status");
                String p_contribution = rs.getString("contribution");
                String p_supporting = rs.getString("supporting");
                Patent patent = new Patent(a_id, p_name, p_type, p_number, p_time, p_status, p_contribution, p_supporting);
                patents.add(patent);
            }
            rs.close();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return patents;
    }
}
